package com.naiterui.ehp.bs.agent.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.agent.entity.AgAgentCustom;

/**
 * @author yiren
 * @since 2022/2/15
 */
@Repository
public interface IAgAgentCustomRepository extends BaseJpaRepository<AgAgentCustom, Long> {

    @Override
    List<AgAgentCustom> findAll();

    AgAgentCustom findFirstByDoctorId(Long doctorId);

    @Query(value = "select " +
            "c.doctorId as customId, " +
            "c.starLev as starLev, " +
            "c.createdAt as inviteTime, " +
            "d.name as customName, " +
            "d.phone as customPhone, " +
            "d.titleId as customTitle, " +
            "d.headUrl as headUrl, " +
            "d.recordStatus as authStatus, " +
            "p.name as departmentName, " +
            "h.name as hospitalName, " +
            "s.recipelTotal as prescriptionTotal, " +
            "(s.consultTotal + s.videoTotal) as consultTotal " +
            "from AgAgentCustom c " +
            "left join Doctor d on d.id = c.doctorId " +
            "left join Department p on p.id = d.departmentId " +
            "left join Hospital h on h.id = d.hospitalId " +
            "left join AgCustomSummary s on s.doctorId = c.doctorId " +
            "where c.agentId=:agentId and c.createdAt < :startTime")
    Page<Map<String,Object>> findCustomPage(@Param("agentId") Long agentId, @Param("startTime") Date startTime, Pageable pageable);

    Long countByProvinceAreaIdIn(List<Long> areaIds);

    AgAgentCustom findByAgentIdAndDoctorId(Long agentId, Long doctorId);

    AgAgentCustom findFirstByDoctorIdAndAgentId(Long doctorId, Long agentId);

    @Query(value = "select count(1) from ag_agent_custom where province_area_id in (select id from ag_area where parent_id=:areaId) and created_at >= :startDate and created_at <= :endDate", nativeQuery = true)
    Long countByAreaIdAndCreatedAt(@Param("areaId") Long areaId, @Param("startDate")String startDate, @Param("endDate")String endDate);

    @Query(value = "select count(1) from ag_agent_custom where province_area_id in (:provinceAreaIds) and created_at >= :startDate and created_at <= :endDate", nativeQuery = true)
    Long countByProvinceAreaIdAndCreatedAt(@Param("provinceAreaIds") List<Long> provinceAreaIds, @Param("startDate")String startDate, @Param("endDate")String endDate);

    @Query(nativeQuery = true,
        value = "SELECT ac.area_id areaId, ac.province_area_id proviceAreaId, ac.agent_id agentId, d.id doctorId, d.`status` status , DATE_FORMAT(ac.created_at, '%Y-%m-%d') as inviteDate "
            + " FROM ag_agent_custom ac LEFT JOIN doctor d ON d.id = ac.doctor_id where d.id is not null")
    List<Object[]> findDoctor();

    @Query(nativeQuery = true,
            value = "select agent_id, count(1) cnt from ag_agent_custom where agent_id in (:agentIds) group by agent_id")
    List<Object[]> findByProvinceAreaIdAndAgentId(@Param("agentIds") List<Long> agentIds);
}
